package Codes_DAO;

import java.sql.*;
import java.util.*;
import java.util.Date;

public class CodeTypeDB implements CodeTypeDAO
  {
   public CodeTypeDB ()
	 {
	 }

   private Connection connect ()
     {
	  Connection con = null;
	  try
	    {
   	     Class.forName("com.mysql.jdbc.Driver");
		 String url      = "jdbc:mysql://localhost:3306/java_class";
		 String username = "root";
		 String password = "mysqlpswd";
		 con             = DriverManager.getConnection(url, username, password);
	    }
	  catch(ClassNotFoundException e)
	    {
		 System.err.println("Database driver not found.");
	    }
	  catch(SQLException e)
	    {
		 System.err.println("Error connecting to the database: " + e);
	    }
	  return con; 
     }

   private void disconnect (Connection con)
     {
	  try
	    {
		 con.close(); 
	    }
	  catch(SQLException e)
	    {
		 System.err.println("Error disconnecting from the database: " + e);
	    }
     }
   
   public ArrayList<CodeType> getCodeTypes ()
	 {
	  try
		{
	     Connection conn = connect();
		 ArrayList<CodeType> cts = new ArrayList<CodeType>();

		 String query = "SELECT Sys, "        +
		                       "Sgmt, "       +
		                       "Desc, "       +
		                       "Len, "        +
		                       "YN_Num, "     +
		                       "YN_UCase "    +
			 			  "FROM cd_typ "      +
					     "ORDER BY Sys ASC, " +
					              "Sgmt ASC";
		 PreparedStatement ps = conn.prepareStatement(query);
		 ResultSet         rs = ps.executeQuery();

		 while(rs.next())
		   {
		    String Sys      = rs.getString("Sys");
		    String Sgmt     = rs.getString("Sgmt");
		    String Desc     = rs.getString("Desc");
		    int    Len      = rs.getInt("Len");
		    String tmpYN    = rs.getString("YN_Num");
		    char   YN_Num   = tmpYN.charAt(0);
		    tmpYN           = rs.getString("YN_UCase");
		    char   YN_UCase = tmpYN.charAt(0);
		    CodeType ct     = new CodeType(Sys, Sgmt, Desc, Len, YN_Num, YN_UCase);
		    cts.add(ct);
		   }
		 rs.close();
		 ps.close();
		 disconnect(conn);
		 return cts;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return null;
		}
	 }

   public CodeType getCodeType (String Sys, String Sgmt)
	 {
	  String tmpSys  = Sys.toUpperCase();
	  String tmpSgmt = Sgmt.toUpperCase();
	  try
		{
	     Connection conn   = connect();
		 String selCodeType = "SELECT Sys, "     +
		                             "Sgmt, "    +
		                             "Desc, "    +
		                             "Len, "     +
		                             "YN_Num, "  +
		                             "YN_UCase " +
				                "FROM cd_typ "   +
				               "WHERE Sys  = ?"  +
				               "  AND Sgmt = ?";
			PreparedStatement ps = conn.prepareStatement(selCodeType);
			ps.setString(1, tmpSys);
			ps.setString(2, tmpSgmt);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
			  {
			   String Desc     = rs.getString("Desc");
			   int    Len      = rs.getInt("Len");
		       String tmpYN    = rs.getString("YN_Num");
		       char   YN_Num   = tmpYN.charAt(0);
		       tmpYN           = rs.getString("YN_UCase");
		       char   YN_UCase = tmpYN.charAt(0);
			   CodeType ct     = new CodeType(Sys, Sgmt, Desc, Len, YN_Num, YN_UCase);
			   rs.close();
			   ps.close();
			   disconnect(conn);
			   return ct;
			  }
			else
			  return null;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	 }

   public boolean addCodeType (CodeType ct)
	 {
	  GregorianCalendar now = new GregorianCalendar();
	  Date today            = now.getTime(); 
	  long nanos            = today.getTime();
	  Timestamp ts          = new Timestamp(nanos);
	  try
		{
	     Connection conn = connect();
		 String insert   = "INSERT "                    +
		                     "INTO cd_typ (Sys, "       +
		                                  "Sgmt, "      +
		                                  "Desc, "      +
		                                  "Len, "       +
		                                  "YN_Num, "    +
		                                  "YN_UCase, "  +
		                                  "TMS_Crtd, "  +
		                                  "User_Crtd, " +
		                                  "TMS_Updt, "  +
		                                  "User_Updt) " +
				           "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		 PreparedStatement ps = conn.prepareStatement(insert);
		 ps.setString(1, ct.getSys());
		 ps.setString(2, ct.getSgmt());
		 ps.setInt(3, ct.getLen());
		 byte tmpYN = (byte) ct.getYN_Num();
		 ps.setByte(4, tmpYN);
		 tmpYN      = (byte) ct.getYN_UCase();
		 ps.setByte(5, tmpYN);
		 ps.setTimestamp(6, ts);
		 ps.setString(7, "USER0001");
		 ps.setTimestamp(8, ts);
		 ps.setString(9, "USER9999");
		 ps.executeUpdate();
		 ps.close();
	     disconnect(conn);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean deleteCodeType (CodeType ct)
	 {
	  try
		{
		 Connection conn = connect();
		 String delete = "DELETE "          +
		                   "FROM cd_typ "   +
                          "WHERE Sys  = ?"  +
                          "  AND Sgmt = ?";
		 PreparedStatement ps = conn.prepareStatement(delete);
		 ps.setString(1, ct.getSys());
		 ps.setString(2, ct.getSgmt());
		 ps.executeUpdate();
		 ps.close();
	     disconnect(conn);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean updateCodeType (CodeType ct)
	 {
	  GregorianCalendar now = new GregorianCalendar();
	  Date today            = now.getTime(); 
	  long nanos            = today.getTime();
	  Timestamp ts          = new Timestamp(nanos);
	  try
		{
		 Connection conn = connect();
		 String update = "UPDATE cd_typ "         +
		                    "SET Desc      = ?, " +
		                        "Len       = ?, " +
		                        "YN_Num    = ?, " +
		                        "YN_UCase  = ?, " +
		                        "TMS_Updt  = ?, " +
					            "User_Updt = ? "  +
                          "WHERE Sys  = ?"  +
                          "  AND Sgmt = ?";
		 PreparedStatement ps = conn.prepareStatement(update);
		 ps.setString(1, ct.getDesc());
		 ps.setInt(2, ct.getLen());
		 byte tmpYN = (byte) ct.getYN_Num();
		 ps.setByte(3, tmpYN);
		 tmpYN      = (byte) ct.getYN_UCase();
		 ps.setByte(4, tmpYN);
		 ps.setTimestamp(5, ts);
		 ps.setString(6, "USER8888");
		 ps.executeUpdate();
		 ps.close();
	     disconnect(conn);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	 }

  }
